package cn.net.autocode.files.excel.handle;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;

public class ExcelAbstractHandle {

	private static Object getCellValue(Cell cell) {

		switch (cell.getCellType()) {
			case NUMERIC:// 证数、小数、日期
				if (DateUtil.isCellDateFormatted(cell)) {
					// 获取 Date 类型的数据
					return cell.getDateCellValue();
				}else{
					return cell.getNumericCellValue();
				}
			case STRING: // 字符串
				return cell.getStringCellValue().trim();
			case FORMULA:// 公式
				return String.valueOf(cell.getCellFormula());
			case BLANK:// 空值
				return null;
			case BOOLEAN:// Boolean
				return cell.getBooleanCellValue();
			case ERROR:// 故障
				return null;
			default:
				return cell.getStringCellValue().trim();
		}
	}

	protected static String getString(Cell cell) {
		Object value = ExcelAbstractHandle.getCellValue(cell);
		if (value == null) {
			return null;
		}
		if (value instanceof Double) {
			return value.toString().trim();
		} else if (value instanceof String) {
			return value.toString().trim();
		} else {
			return null;
		}
	}

	protected static Date getDate(Cell cell, String dateFormat) {
		String value = cell.getStringCellValue();
		Date tfDate;
		if (value.trim().equals("/") || value.trim().isEmpty()) {
			return null;
		}

		if (value.contains(".")) {

			if (value.matches("^\\d{4}\\.\\d{2}$")) {
				value = value + ".01";
			}

			tfDate = DateTools.parseDate(value, dateFormat);
		} else {
			try {
				int valueNum = Integer.parseInt(value);
				Calendar c = Calendar.getInstance();
				c.set(1900, 0, 1);
				c.add(Calendar.DATE, valueNum - 2);
				tfDate = c.getTime();
			} catch (NumberFormatException e) {
				value = value.replace("x", "1");

				tfDate = DateTools.parseDate(value, dateFormat);
			}
		}

		return tfDate;
	}

	protected static Date getNumDate(Cell cell, String dateFormat) {
		String value = cell.getStringCellValue();
		Date tfDate = null;
		if (value.trim().equals("/")) {
			return null;
		}
		try {
			tfDate = DateTools.parseDate(value, dateFormat);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return tfDate;
	}

	protected static Integer getInt(Cell cell) {
		Object value = ExcelAbstractHandle.getCellValue(cell);
		if (value == null) {
			return null;
		}
		if (value instanceof Double) {
			return ((Double) value).intValue();
		} else if (value instanceof String) {
			try {
				return Integer.valueOf(value.toString());
			} catch (NumberFormatException e) {
				// TODO Auto-generated catch block
				return null;
			}

		} else {
			return null;
		}
	}

	/**
	 * 获取当前cell合并的行数
	 * @param cell  当前cell
	 * @param sheet 当前sheet
	 */
	protected static int getMergeRowNum(Cell cell, Sheet sheet) {
		int mergeSize = 1;
		List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
		for (CellRangeAddress cellRangeAddress : mergedRegions) {
			if (cellRangeAddress.isInRange(cell)) {
				// 获取合并的行数
				mergeSize = cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() + 1;
				break;
			}
		}
		return mergeSize;
	}

	/**
	 * 获取合并的列数
	 * @param cell  当前cell
	 * @param sheet 当前sheet
	 */
	protected static int getMergeColumNum(Cell cell, Sheet sheet) {
		int mergeSize = 1;
		List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
		for (CellRangeAddress cellRangeAddress : mergedRegions) {
			if (cellRangeAddress.isInRange(cell)) {
				// 获取合并的列数
				mergeSize = cellRangeAddress.getLastColumn() - cellRangeAddress.getFirstColumn() + 1;
				break;
			}
		}
		return mergeSize;
	}


	/**
	 * HSSFWorkbook读取97-2003格式 ，XSSFWorkbook读取2007-2013格式
	 * @param inputStream 文件信息
	 * @return excel转换位JSONArray的数据
	 */
	protected static JSONArray readExcel(InputStream inputStream) {

		JSONArray datas = new JSONArray();

		try {
			Workbook wb = new XSSFWorkbook(inputStream);
			//获取第一个sheet
			Sheet sheet = wb.getSheetAt(0);

			//获取合并单元格
			List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
			Map<String,CellRangeAddress> mergedMap = new HashMap<>();
			for(CellRangeAddress rangeAddress:mergedRegions){
				String formatAsString = rangeAddress.formatAsString();
				mergedMap.put(formatAsString.substring(0,formatAsString.indexOf(":")),rangeAddress);
			}
			//获取最大行数
			int rowNums = sheet.getLastRowNum();

			for (int i = 0; i<=rowNums; i++) {
				Row row = sheet.getRow(i);
				if(row !=null){
					JSONArray cellArray = new JSONArray();
					int columns = row.getLastCellNum();
					for (int j=0;j<columns;j++){
						Cell cell = row.getCell(j);

						if (cell==null){
							continue;
						}
						JSONObject json =new JSONObject();
						CellAddress address = cell.getAddress();
						json.put("address",address.formatAsString());
						json.put("rowIndex",cell.getRowIndex());
						json.put("columnIndex",cell.getColumnIndex());
						Object value = ExcelAbstractHandle.getCellValue(cell);
						json.put("value",value);
						if(value!=null&&!"".equals(value)){
							if(mergedMap.containsKey(address.formatAsString())){
								json.put("mergedCell","Y");
								json.put("firstRow",mergedMap.get(address.formatAsString()).getFirstRow());
								json.put("firstColumn",mergedMap.get(address.formatAsString()).getFirstColumn());
								json.put("lastRow",mergedMap.get(address.formatAsString()).getLastRow());
								json.put("lastColumn",mergedMap.get(address.formatAsString()).getLastColumn());
							}else{
								json.put("mergedCell","N");
							}
							cellArray.add(json);
						}else{
							boolean isInRange = false;
							for(CellRangeAddress rangeAddress:mergedRegions){
								if (rangeAddress.isInRange(cell)){
									isInRange = true;
								}
							}
							if(!isInRange){
								cellArray.add(json);
							}
						}

					}
					datas.add(cellArray);
				}else{
					break;
				}
			}

		} catch (IOException e) {
			//e.printStackTrace();
		}

		return datas;
	}


	protected static byte[] writeExcel(JSONObject template,JSONArray dataArray) {
		Workbook workbook = new XSSFWorkbook();

		/**
		 * 通过setExcelCellStyle()方法来设置单元格样式，具体步骤如下：
		 * 创建HSSFCellStyle对象，即cellStyle。
		 * 设置单元格边框样式，可以通过setBorderBottom()、setBorderLeft()、setBorderRight()和setBorderTop()方法分别设置下边框、左边框、右边框和上边框。同时，还可以通过setBottomBorderColor()方法设置边框颜色。
		 * 设置文字居中，通过setAlignment()方法设置水平对齐方式，这里设置为居中。
		 * 设置字体样式，首先创建HSSFFont对象font，然后通过setColor()、setFontName()、setFontHeightInPoints()、setBoldweight()、setWrapText()和setFillForegroundColor()方法分别设置字体颜色、字体样式、字体大小、加粗、自动换行和背景色。
		 * 最后，将设置好的font应用到cellStyle上，并将cellStyle应用到单元格上。
		 */
		//普通表头
		CellStyle normalStyle = workbook.createCellStyle();
		normalStyle.setBorderBottom(BorderStyle.THIN);// 设置下边框
		normalStyle.setBorderLeft(BorderStyle.THIN);// 设置左边框
		normalStyle.setBorderRight(BorderStyle.THIN);// 设置右边框
		normalStyle.setBorderTop(BorderStyle.THIN);// 设置上边框
		normalStyle.setAlignment(HorizontalAlignment.CENTER);
		normalStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		CellStyle normalStyle2 = workbook.createCellStyle();
		normalStyle2.setBorderBottom(BorderStyle.THIN);// 设置下边框
		normalStyle2.setBorderLeft(BorderStyle.THIN);// 设置左边框
		normalStyle2.setBorderRight(BorderStyle.THIN);// 设置右边框
		normalStyle2.setBorderTop(BorderStyle.THIN);// 设置上边框
		normalStyle2.setAlignment(HorizontalAlignment.RIGHT);
		normalStyle2.setVerticalAlignment(VerticalAlignment.CENTER);


		// 获取第一个 Sheet
		Sheet sheet = workbook.createSheet("Sheet1");

		JSONArray titleCells = template.getJSONArray("titleCells");
		/*
		 * 通过setColumnWidth方法设置列的宽度。
		 * 参数分别为列索引和宽度值，宽度值以 1/256 个字符为单位。
		 * 你可以根据需要设置不同列的宽度。
		 */
		for (int i = 0; i < titleCells.size(); i++) {
			JSONObject json = titleCells.getJSONObject(i);
			int width = json.getIntValue("width");
			if(width>=5){
				sheet.setColumnWidth(i, width * 256);
			}
		}
		//创建表头
		int titleNo = template.getIntValue("titleNo");

		if(titleNo>=2){
			//第一行，合并信息
			Row headerRow0 = sheet.createRow(0);
			headerRow0.setHeightInPoints(18);
			Cell cell = headerRow0.createCell(0);
			cell.setCellValue(template.getString("label"));
			cell.setCellStyle(normalStyle);
			if(titleCells.size()>1){
				// 设置合并单元格的起始行
				CellRangeAddress cellRangeAddress = new CellRangeAddress(0,0,0,titleCells.size()-1);
				sheet.addMergedRegion(cellRangeAddress);
				RegionUtil.setBorderTop(BorderStyle.THIN,cellRangeAddress,sheet);
				RegionUtil.setBorderRight(BorderStyle.THIN,cellRangeAddress,sheet);
				RegionUtil.setBorderBottom(BorderStyle.THIN,cellRangeAddress,sheet);
				RegionUtil.setBorderLeft(BorderStyle.THIN,cellRangeAddress,sheet);
			}
		}

		if(titleNo==3){
			//第一行，合并信息
			Row headerRow1 = sheet.createRow(1);
			headerRow1.setHeightInPoints(18);
			Cell cell = headerRow1.createCell(0);
			cell.setCellValue(template.getString("label"));
			cell.setCellStyle(normalStyle2);
			// 设置合并单元格的起始行
			if(titleCells.size()>1){
				// 设置合并单元格的起始行
				CellRangeAddress cellRangeAddress = new CellRangeAddress(1,1,0,titleCells.size()-1);
				sheet.addMergedRegion(cellRangeAddress);
				RegionUtil.setBorderTop(BorderStyle.THIN,cellRangeAddress,sheet);
				RegionUtil.setBorderRight(BorderStyle.THIN,cellRangeAddress,sheet);
				RegionUtil.setBorderBottom(BorderStyle.THIN,cellRangeAddress,sheet);
				RegionUtil.setBorderLeft(BorderStyle.THIN,cellRangeAddress,sheet);
			}
		}

		//创建列头
		Row titleRow = sheet.createRow(titleNo-1);
		titleRow.setHeightInPoints(18);
		for (int i = 0; i < titleCells.size(); i++) {
			JSONObject json = titleCells.getJSONObject(i);
			Cell cell = titleRow.createCell(i);
			cell.setCellValue(json.getString("label"));
			cell.setCellStyle(normalStyle);
		}

		//
		for (int i = 0; i < dataArray.size(); i++) {
			JSONObject data = dataArray.getJSONObject(i);
			Row row = sheet.createRow(titleNo+i);
			row.setHeightInPoints(18);
			for (int j = 0; j < titleCells.size(); j++) {
				JSONObject title = titleCells.getJSONObject(j);
				Cell cell = row.createCell(j);
				Object value = data.get(title.getString("code"));
				if(value!=null){
					if(value instanceof String dd){
						cell.setCellValue(dd);
					}else if(value instanceof Double dd){
						cell.setCellValue(dd);
					}else if(value instanceof Float dd){
						cell.setCellValue(dd);
					}else if(value instanceof Integer dd){
						cell.setCellValue(dd);
					}else if(value instanceof Boolean dd){
						cell.setCellValue(dd);
					}else if(value instanceof Date dd){
						cell.setCellValue(DateTools.format(dd,title.getString("format")));
					}else if(value instanceof LocalDateTime dd){
						cell.setCellValue(DateTools.format(dd,title.getString("format")));
					}else if(value instanceof LocalDate dd){
						cell.setCellValue(DateTools.format(dd,title.getString("format")));
					}else{
						cell.setCellValue(value.toString());
					}
				}
				cell.setCellStyle(normalStyle);
			}
		}


		//文件导出
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		try {
			workbook.write(outputStream);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}

        return outputStream.toByteArray();
	}



}
